<?php
/**
 +------------------------------------------------------------------------------
 * Rpt Base Model模型扩展类
 +------------------------------------------------------------------------------
 * @author    Weiping
 +------------------------------------------------------------------------------
 */
class RptBaseModel extends NewzanModel {
    
	/** 报表标签 */
	public static $LABELS =  array (
				'impressions' => '展现量',
				'click' => '点击量',
				'cost' => '花费',
				'sales' => '销售额',
				'pay_count' => '成交笔数',
				'roi' => '投入产出比',
				'cpc' => '平均点击花费',
				'ctr' => '点击率',
				'cpm' => '千次展现费',
				'direct_pay' => '直接成交额',
				'indirect_pay' => '间接成交额',
				'direct_pay_count' => '直接成交笔数',
				'indirect_pay_count' => '间接成交笔数',
				'fav_shop_count' => '店铺收藏量',
				'fav_item_count' => '商品收藏量',
				'avgpos' => '平均展现排名',
				'aclick'=>'总点击量',
				'cvr' => '点击转化率'
		);
	
	/** @var array(string) 默认统计字段 */
	public static $DEFAULT_STAT_FIELDS = array('cost', 'sales','impressions', 'click', 'pay_count', 'roi', 'cpc', 
			'ctr', 'cpm', 'direct_pay', 'indirect_pay', 'direct_pay_count', 'indirect_pay_count', 
			'fav_shop_count', 'fav_item_count' , 'cvr', 'fav_total_count');
	
	/** @var array (k=>v) Summary stats 所用的数据计算方式. */
	public static $DB_SUM_FIELDS = array(
			'cost' => 'SUM(cost)/100',
			'sales' => 'SUM(direct_pay + indirect_pay)/100',
			'pay_count' => 'SUM(direct_pay_count + indirect_pay_count)',
			'roi' => 'FORMAT(SUM(direct_pay + indirect_pay)/SUM(cost),2)',
			'cpc' => 'SUM(cost)/100/SUM(click)',
			'impressions' => 'SUM(impressions)',
			'click' => 'SUM(click)',
			'ctr' => '100*SUM(click)/SUM(impressions)',
			'cpm' => '10*SUM(cost)/SUM(impressions)',
			'direct_pay' => 'SUM(direct_pay)/100',
			'indirect_pay' => 'SUM(indirect_pay)/100',
			'direct_pay_count' => 'SUM(direct_pay_count)',
			'indirect_pay_count' => 'SUM(indirect_pay_count)',
			'fav_shop_count' => 'SUM(fav_shop_count)',
			'fav_item_count' => 'SUM(fav_item_count)',
			'fav_total_count' => 'SUM(fav_shop_count)+SUM(fav_item_count)',
			'avgpos' => 'AVG(avgpos)',
			'aclick'=>'SUM(aclick)',
			'cvr' => '100*SUM(direct_pay_count + indirect_pay_count)/SUM(click)',
	);

	/** @var array (k=>v) Average stats 所用的数据计算方式. */
	public static $DB_AVG_FIELDS = array(
			'cost' => 'AVG(cost)/100',
			'sales' => 'AVG(direct_pay + indirect_pay)/100',
			'pay_count' => 'AVG(direct_pay_count + indirect_pay_count)',
			'roi' => 'FORMAT(SUM(direct_pay + indirect_pay)/SUM(cost),2)',
			'cpc' => 'SUM(cost)/100/SUM(click)',
			'impressions' => 'AVG(impressions)',
			'click' => 'AVG(click)',
			'ctr' => '100*SUM(click)/SUM(impressions)',
			'cpm' => '10*SUM(cost)/SUM(impressions)',
			'direct_pay' => 'AVG(direct_pay)/100',
			'indirect_pay' => 'AVG(indirect_pay)/100',
			'direct_pay_count' => 'AVG(direct_pay_count)',
			'indirect_pay_count' => 'AVG(indirect_pay_count)',
			'fav_shop_count' => 'AVG(fav_shop_count)',
			'fav_item_count' => 'AVG(fav_item_count)',
			'avgpos' => 'AVG(avgpos)',
			'aclick'=>'AVG(aclick)', 
			'cvr' => '100*SUM(direct_pay_count + indirect_pay_count)/SUM(click)',
	);

	/** @var array (k=>v) Series stats 所用的数据计算方式. */
	public static $DB_SER_FIELDS = array(
			'cost' => 'cost/100',
			'sales' => '(direct_pay+indirect_pay)/100',
			'roi' => '(direct_pay+indirect_pay)/cost',
			'cpc' => 'cost/100/click',
			'ctr' => '100*click/impressions',
			'cpm' => '10*cost/impressions',
			'direct_pay' => 'direct_pay/100',
			'indirect_pay' => 'indirect_pay/100',
			'pay_count' => 'direct_pay_count+indirect_pay_count',
			'cvr' => '100*(direct_pay_count+indirect_pay_count)/click',
	);
	
	/** @var array 淘宝API支持的时间段 */
	public static $_TIMESLOTS = array('DAY', '7DAY', '15DAY', '30DAY', '90DAY');
	/** @var array 淘宝API返回的搜索来源=>保存到数据组中的值 */
	public static $_SEARCH_TYPES = array('SEARCH'=>0, 'CAT'=>1, 'NOSEARCH'=>2, 'SUMMARY' => 3, 'summary' => '0,1,2');
	/** @var array 淘宝API返回的来源=>保存到数据组中的值 */
	public static $_SOURCE_TYPES = array(1 => 1, 2 => 2, 'SUMMARY' => 3, 4 => 4, 5 => 5);
	/** @var array 淘宝API调用允许的来源 */
	public static $_SOURCES = array('1,2', '4,5', 'SUMMARY', 'summary'=> '1,2,4,5', 'pc' => '1,2', 'wireless' => '4,5');
	/** @var int 统计数据最后保留的小数位精度 */
	const ROUND_PRECISION = 2;
	
	public function _initialize(){
        parent::_initialize();
	}
	
	/**
	 * 根据不同的子类, 和给定的ID列表获取 summary, average 或者 series 统计信息.
	 *  
	 * @param int $userId
	 * @param array(number)|number $ids 不同的子类需要给不同的IDs. 具体如下: <br/>
	 * <ul>
				<li> 'RptAdgroupCreativeModel'	=> 'creative_id'; </li>
				<li> 'RptAdgroupModel' => 'adgroup_id'; </li>
				<li> 'RptAdgroupPlaceModel' => 'place_id', //TODO enough? </li>
				<li> 'RptCampaignModel' => 'campaign_id', </li>
				<li> 'RptCustomerModel' => 'taobao_user_id', </li>
				<li> //'RptDemographicModel' => '' // TODO what to do here? </li>
				<li> 'RptKeywordModel' => 'keyword_id', </li>
		</ul>
	 * @param array(string)|string $indicators
	 * @param string $from
	 * @param string $to
	 * @param string $type summary,average,series 三者之一或其组合, 但是summary,average不能同时存在.
	 * @return array(2D)
	 */
	public function getStats($userId, $ids, $indicators = 'all', $from = null, $to = null, $type='summary,series', $sort = array())
	{
		$this->_checkDates($from, $to);
		$idFields = array(
				'RptAdgroupCreativeModel'	=> 'creative_id',
				'RptAdgroupModel' => 'adgroup_id',
				'RptAdgroupPlaceModel' => 'place_id', //TODO enough?
				'RptCampaignModel' => 'campaign_id',
				'RptCustomerModel' => 'taobao_user_id',
				//'RptDemographicModel' => '' // TODO what to do here?
				'RptKeywordModel' => 'keyword_id',
		);
		
		$class = get_class($this);
		$idField = $idFields[$class];
		$return = array (
				$idField => $ids,
				'user_id' => $userId,
				'from' => strtotime($from . ' UTC'),
				'to' => strtotime($to . ' UTC'),
				//'max_date' => $this->getMaxDate($userId, $idField, $ids, $indicators, $from, $to)
		);
		
		$types = explode(',', $type);
		if(in_array('summary', $types)){
			$summary = $this->getSummaryStats($userId, $idField, $ids, $indicators, $from, $to, true, '', 'summary', $sort);
			$return['summary'] = $summary;
		} else if(in_array('average', $type)) {
			$summary = $this->getSummaryStats($userId, $idField, $ids, $indicators, $from, $to, true, '', 'average');
			$return['average'] = $summary;
		}
		if(in_array('series', $types)){
			$stats = $this->getSeriesStats($userId, $idField, $ids, $indicators, $from, $to);
			$return['series'] = $stats;
		}
		if(in_array('pc', $types)) {
			$summary = $this->getSummaryStats($userId, $idField, $ids, $indicators, $from, $to, true, '', 'pc');
			$return['pc'] = $summary;
		}
		if(in_array('wireless', $types)) {
			$summary = $this->getSummaryStats($userId, $idField, $ids, $indicators, $from, $to, true, '', 'wireless');
			$return['wireless'] = $summary;
		}
		return $return;
	}
	
	/**
	 * 
	 * 得到一段时间内最大的日期来判断最后更新到的时间
	 * @param int $user_id
	 * @param string $idField ， 
	 * @param array(int) $ids
	 * @param string $from
	 * @param string $to
	 */
	public function getMaxDate($user_id, $idField, $ids, $statFields=null, $from = null, $to = null){
		if(!$ids) return 0;
	    $this->_checkStatParams($user_id, $idField, $ids, $statFields, $from, $to);
        //$sqlWhere = " WHERE taobao_user_id=$user_id AND $idField IN (" . join(',', $ids) . ") AND `date` BETWEEN '$from' AND '$to' AND source =3";
	    $sqlWhere = " WHERE taobao_user_id=$user_id AND $idField IN (" . join(',', $ids) . ")";
	    $sql = 'SELECT MAX(date) as max_date, MIN(date) as min_date FROM `__TABLE__`' . $sqlWhere;
	    $res = $this->query($sql);
	    $max_date = $res[0]['max_date'] ? $res[0]['max_date'] : false;
	    return $max_date;
	}
	
	
	/**
	 * 获取给定指标在给定时间范围内的序列值. 
	 * 
	 * @param int $user_id
	 * @param string $idField
	 * @param array(int)|int $ids 暂不支持大整型数组, 如keyword_id.
	 * @param array(string)|string $statFields
	 * @param string $from
	 * @param string $to
	 * @throws Exception
	 * @return array2D 返回填充好的序列. 每个指标为一行.
	 *  返回值的格式为: 
	 * 		array( 
	 * 			label1 => array(v1, v2, v3, ...), 
	 * 			... )
	 */
	public function getSeriesStats($user_id, $idField, $ids, $statFields, $from = null, $to = null) {
		$this->_checkStatParams($user_id, $idField, $ids, $statFields, $from, $to);
		if(!$ids) 
			return array();
		if(count($ids) > 1) {
			throw_exception('目前只支持$ids为一个元素的数组.');
		}
		
// 		$class = get_class($this);
// 		$basicFields = array(
// 				'RptAdgroupModel' => array('date'), // taobao_user_id', 'campaign_id', 'adgroup_id', '
// 				// TODO 为每个支持的类都设定基础列
// 		);
// 		$sqlFields = join(',', $basicFields[$class]);
		$sqlFields = 'date';
		$class = get_class($this);

		if($class == 'RptKeywordModel'){
			$sqlFields .= $this->_mapStatField($sqlFields, $statFields, self::$DB_SUM_FIELDS);
			$sqlWhere = "taobao_user_id=$user_id AND $idField IN (" . join(',', $ids) . ") AND `date` BETWEEN '$from' AND '$to' AND source IN (1,2,4,5)";
			$sql = "SELECT $sqlFields FROM `__TABLE__` WHERE $sqlWhere GROUP BY `date` ORDER BY `date`";
		}else{
			$sqlFields .= $this->_mapStatField($sqlFields, $statFields, self::$DB_SUM_FIELDS);
			$sqlWhere = "taobao_user_id=$user_id AND $idField IN (" . join(',', $ids) . ") AND `date` BETWEEN '$from' AND '$to' AND source IN (".self::$_SOURCES['summary'].") GROUP BY date";
			$sql = "SELECT $sqlFields FROM `__TABLE__` WHERE $sqlWhere ORDER BY `date`";
		}
		
		$res = $this->query($sql);
// 		Log::record($this->getLastSql(), Log::DEBUG);
		
		// 2D数组转置: 将数据由每天一行转化为每个指标(field)一行, 方便显示
		$labels = self::$LABELS;
		$serieses = array();
		if($res) {
			foreach($res as $record) {
				foreach($statFields as $field) {
					$val = $record[$field] !== null ? round($record[$field], self::ROUND_PRECISION) : 0;
					// 将序列的行转化为列并加上转化为UTC timestamp的时间!
					$serieses[$labels[$field]][] = array(strtotime($record['date'] . ' UTC'), $val);
				}
			}
		} else {
			foreach($statFields as $field) {
				$serieses[$labels[$field]] = array();
			}
		}
		
		// 将每个指标不存在的日期的值填充为0.
		$end_ts = strtotime("$to UTC");
		foreach($serieses as $lable => $series) {
			$last_ts = strtotime("$from UTC") - 86400;
			$filled_series = array();
			foreach($series as $dot) {
				$curr_ts = $dot[0];
				for($ts = $last_ts + 86400; $ts < $curr_ts; $ts += 86400) { // 不存在的点用0填充
					$filled_series[] = array($ts * 1000, 0);
				}
				$filled_series[] = array($curr_ts * 1000, floatval($dot[1]));
				$last_ts = $curr_ts;
			}
			for($ts = $last_ts + 86400; $ts <= $end_ts; $ts += 86400) {
				$filled_series[] = array($ts * 1000, 0);
			}
			
			$serieses[$lable] = $filled_series;
		}
		
		return $serieses;
		
	}
	
	/**
	 * 获取给定类报表给定时间段内的统计信息. <strong>注意</strong>: 所有的金额都以元为单位.
	 * 
	 * @param int $user_id
	 * @param string $idField <strong>ID字段必须为表的主键。比如查询keywords的数据时， $idField必须为'keyword_id'。
	 * 		否则最后数据补零的时候会出错！！！</strong>
	 * @param array(number)|number $ids 可支持大整型（64位）数字如果需要结果以二维数组返回， 这里需给数组， 即使只有一个ID。
	 * 	如果为单个number（非数组）， 返回的结果将为这个id对应的一维数组。 
	 * @param array(string)|string $statFields
	 * @param string $from
	 * @param string $to
	 * @param string $addtionalWhere 附加where条件. 必须保证是有效的. 示例: 'adgroup_id=1343848'
	 * @param string $type = 'summary' 获取总计数据还是平均数据。 取值范围为['summary'|'average']
	 * @throws Exception
	 * @return array(1D|2D) 如果$ids为一个数字（不为数组， 且不含','），返回1维数组。 否则返回2维数组(id => array()). 
	 * 没有找到的ID的记录, 会用0数组填充. 如果$ids为空, 返回空数组array().
	 */
	public function getSummaryStats($user_id, $idField, $ids, $statFields, $from = null, $to = null, $fillNonExist = true, $addtionalWhere = '',$type = 'summary', $sort = array())
	{
		if(!$ids) return array();
		$ret1D = !is_array($ids) && strpos($ids, ',') === false;
		
		$this->_checkStatParams($user_id, $idField, $ids, $statFields, $from, $to);
		
		$sqlFields = $idField;
		$this->_mapStatField($sqlFields, $statFields, in_array($type, array('summary', 'pc', 'wireless')) ? self::$DB_SUM_FIELDS : self::$DB_AVG_FIELDS);

		$sqlWhere = "taobao_user_id=$user_id AND " . ($idField == 'taobao_user_id' ? '' : ("$idField IN (" . join(',', $ids) . ") AND ")) . "`date` BETWEEN '$from' AND '$to' ";
		if($addtionalWhere)
			$sqlWhere .= " AND ($addtionalWhere)";
		// rpt_demographic和rpt_adgroup_place表不能用source=3!!!
		$class = get_class($this);
		if($class != 'RptAdgroupPlaceModel' && $class != 'RptDemographicModel') {
			if($class != 'RptKeywordModel') {
				// if($type == 'pc' || $type == 'wireless')
					$sqlWhere .= ' AND source IN (' . self::$_SOURCES[$type] . ')';
			}
			else 
				$sqlWhere .= ' AND source IN (' . self::$_SOURCES[$type] . ')';
			// RptCustomerModel doesn't have 'search_type' and RptKeywordModel has details in SEARCH,NONSEARCH,CAT
			// if($class == 'RptCampaignModel') {
			// 	$sqlWhere .= ' AND search_type IN (' . self::$_SEARCH_TYPES['summary'] . ')';
			// } elseif($class != 'RptCustomerModel' && $class != 'RptKeywordModel') { 
			// 	$sqlWhere .= ' AND search_type=' . self::$_SEARCH_TYPES['SUMMARY'];
			// }
		}
		
		
		switch( $class ){
		    case 'RptAdgroupPlaceModel':
		        $sql = "SELECT $sqlFields FROM `__TABLE__` WHERE $sqlWhere GROUP BY place_id";
		        $idField = 'place_id';
		    break;
		    
		    case 'RptAdgroupCreativeModel':
		        $sql = "SELECT $sqlFields FROM `__TABLE__` WHERE $sqlWhere GROUP BY creative_id";
		        $idField = 'creative_id'; 
		    break;
		    
		    default:
		        $sql = "SELECT $sqlFields FROM `__TABLE__` WHERE $sqlWhere GROUP BY $idField ";
		        if($sort && isset($sort['sort']) && $sort['sort'] != 'status') {
		        	$sql.=" order by {$sort['sort']} {$sort['asc']}";
		        } else if(strpos($sqlFields,'sales') &&strpos($sqlFields,'click') ){
		        	$sql.=" order by sales desc, click desc";
		        }
		    break;
		    
		}
		$res = $this->query($sql);
		$newRes = array();
		// 处理查询得到的数据, null用0代替, float精确到小数点后2位.
		if( !empty($res) ) {
			$id = '';
			foreach($res as $record) {
				$newRecord = array();
				foreach($record as $k => $ele) {
					if($k != $idField) 
						$newRecord[$k] = $ele === null ? 0 : round($ele, self::ROUND_PRECISION);
					else
						$id = $ele;
				}
				$newRes[$id] = $newRecord;
			}
		}
		
		// 没有查到的ID, 其数据用0数组补齐
		if($fillNonExist) {
			$blankRecord = array_fill_keys($statFields, 0);
			if(count($newRes) != count($ids)) {
				foreach($ids as $id) {
					if(!isset($newRes[$id])) {
						$blankRecord[$idField] = $id;
						$newRes[$id] = $blankRecord;
					}
				}
			}
		}
		
		if($ret1D) {
			return count($newRes) ? $newRes[$ids[0]] : array();
		} else {
			return $newRes;
		}
		
	}
	
	/** 
	 * 检查参数
	 */
	protected function _checkStatParams(&$user_id, &$idField, &$ids, &$statFields, &$from, &$to) {
		// $user_id = intval($user_id);
		$class = get_class($this);
		
		//$idField = mysql_real_escape_string($idField);

		if(!$idField) {
			throw new Exception('参数错误.', RetStatus::PARAM_ERR);
		}
		
		if($ids) {
			if(!is_array($ids))
				$ids = explode(',', $ids);
			if($idField != 'taobao_user_id')
				$ids = check_int_arr($ids , $class == 'RptKeywordModel');
			

		}

		$this->_checkStatFields($statFields);
		
		$this->_checkDates($from, $to);
	}
	
	/**
	 * 检查日期是否设置, 如果没有, 设置为过去的1周.
	 * @param string $from
	 * @param string $to
	 */
	protected function _checkDates(&$from, &$to) {
		if (!$from || !$to) {
			$from = date('Y-m-d', strtotime('-7 day'));
			$to = date('Y-m-d', strtotime('-1 day'));
		}
		
		//如果时间超过了90天，抛出异常
		if( strtotime( $from ) < date2time(-90) ){
		    throw new NewzanException( '只能查询90天之内的数据' , RetStatus::INNER_ERR);
		}
		
		if( strtotime( $to ) > date2time(-1) ){
		    throw new NewzanException( '只能查询到昨天之内的数据' , RetStatus::INNER_ERR );
		}		
		
	}
	
	/**
	 * 检查指标列是否合法
	 * @param array(string)& $statFields [写] 要检查的列, 检查过的列就在这个参数上直接变更.
	 */
	protected function _checkStatFields(&$statFields) {
		$avilable_indicators = array_keys(self::$LABELS);
		
		if ($statFields && $statFields != 'all') {
			if (!is_array($statFields)) {
				$statFields = explode(',', $statFields);
			}
			$statFields = array_intersect($avilable_indicators, $statFields);
		}
		//if nothing , then use default indicators;
		if (!$statFields || $statFields == 'all') {
			$statFields = self::$DEFAULT_STAT_FIELDS; //array ('sales', 'cost');
		}
	}
	
	/**
	 * 将指标列拼成sql的field段.  
	 * @param string& $sqlFields [写]初始sql字段, 最后的结果也是这个参数.
	 * @param array(string) $statFields 统计字段名
	 * @param array(k => v) 短字段 => 长计算公式
	 */
	protected function _mapStatField(&$sqlFields, $statFields, $map) {
		foreach ($statFields as $field) {
			if($sqlFields)
				$sqlFields .= ',';
			if(isset($map[$field])) {
				$sqlFields .= $map[$field] . " AS $field";
			} else {
				$sqlFields .= '`' . mysql_real_escape_string($field) . '`';
			}
		}
		$class = get_class($this);
		switch( $class ){
		    case 'RptAdgroupPlaceModel':
		        $sqlFields .= ',place_id'; 
		    break;
		    
		    case 'RptAdgroupCreativeModel':
		        $sqlFields .= ',creative_id';
		    break;
		}
	}
	
}
